{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ffdb5b70",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d5823b66",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 数据库地址：数据库放在上一级目录下\n",
    "db_path = os.path.join(os.path.dirname(os.getcwd()), \"data.db\")\n",
    "engine_path = \"sqlite:///\" + db_path\n",
    "# 创建数据库引擎\n",
    "engine = create_engine(engine_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5d8f2ca9",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "select \n",
    "*\n",
    "from\n",
    "shopRefuse\n",
    "\"\"\"\n",
    "\n",
    "df = pd.read_sql(sql, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1e9beec3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>shopid</th>\n",
       "      <th>create_time</th>\n",
       "      <th>total_num</th>\n",
       "      <th>td_num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1391</th>\n",
       "      <td>1391</td>\n",
       "      <td>25</td>\n",
       "      <td>2022-02-19 00:00:00.000000</td>\n",
       "      <td>115</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>42</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-02-14 00:00:00.000000</td>\n",
       "      <td>144</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1025</th>\n",
       "      <td>1025</td>\n",
       "      <td>19</td>\n",
       "      <td>2022-01-20 00:00:00.000000</td>\n",
       "      <td>216</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3286</th>\n",
       "      <td>3286</td>\n",
       "      <td>59</td>\n",
       "      <td>2022-02-10 00:00:00.000000</td>\n",
       "      <td>173</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1324</th>\n",
       "      <td>1324</td>\n",
       "      <td>24</td>\n",
       "      <td>2022-02-08 00:00:00.000000</td>\n",
       "      <td>197</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      index  shopid                 create_time  total_num  td_num\n",
       "1391   1391      25  2022-02-19 00:00:00.000000        115      13\n",
       "42       42       1  2022-02-14 00:00:00.000000        144      24\n",
       "1025   1025      19  2022-01-20 00:00:00.000000        216      22\n",
       "3286   3286      59  2022-02-10 00:00:00.000000        173      11\n",
       "1324   1324      24  2022-02-08 00:00:00.000000        197      17"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6bf4838a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd8d588d",
   "metadata": {},
   "source": [
    "### 添加周维度"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ccc6eef7",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime,timedelta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "2a7274be",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_monday_to_sunday(today, weekly=0):\n",
    "    \"\"\"\n",
    "    :function: 获取指定日期的周一和周日的日期\n",
    "    :param today: '2021-11-16'; 当前日期：today = datetime.now().strftime('%Y-%m-%d')\n",
    "    :param weekly: 获取指定日期的上几周或者下几周，weekly=0当前周，weekly=-1上一周，weekly=1下一周\n",
    "    :return: 返回指定日期的周一和周日日期\n",
    "    :return_type: tuple\n",
    "    \"\"\"\n",
    "    last = weekly * 7\n",
    "    today = datetime.strptime(str(today), \"%Y-%m-%d\")\n",
    "    monday = datetime.strftime(today - timedelta(today.weekday() - last), \"%Y-%m-%d\")\n",
    "    monday_ = datetime.strptime(monday, \"%Y-%m-%d\")\n",
    "    sunday = datetime.strftime(monday_ + timedelta(monday_.weekday() + 6), \"%Y-%m-%d\")\n",
    "    return \"{0}|{1}\".format(monday, sunday)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0c3c3fbe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2021-12-27|2022-01-02'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_monday_to_sunday(\"2022-01-01\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "268a8bdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2[\"week_range\"] = df2[\"create_time\"].map(lambda x:get_monday_to_sunday(str(x)[:10]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "92fdf28b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2022-01-24|2022-01-30    560\n",
       "2022-02-14|2022-02-20    560\n",
       "2022-01-31|2022-02-06    560\n",
       "2022-02-21|2022-02-27    560\n",
       "2022-02-07|2022-02-13    560\n",
       "2022-01-10|2022-01-16    560\n",
       "2022-01-03|2022-01-09    560\n",
       "2022-01-17|2022-01-23    560\n",
       "Name: week_range, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2[\"week_range\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "5cceca3f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>shopid</th>\n",
       "      <th>create_time</th>\n",
       "      <th>total_num</th>\n",
       "      <th>td_num</th>\n",
       "      <th>week_range</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4349</th>\n",
       "      <td>4349</td>\n",
       "      <td>78</td>\n",
       "      <td>2022-02-09 00:00:00.000000</td>\n",
       "      <td>251</td>\n",
       "      <td>28</td>\n",
       "      <td>2022-02-07|2022-02-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1120</th>\n",
       "      <td>1120</td>\n",
       "      <td>21</td>\n",
       "      <td>2022-01-03 00:00:00.000000</td>\n",
       "      <td>193</td>\n",
       "      <td>27</td>\n",
       "      <td>2022-01-03|2022-01-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <td>392</td>\n",
       "      <td>8</td>\n",
       "      <td>2022-01-03 00:00:00.000000</td>\n",
       "      <td>173</td>\n",
       "      <td>26</td>\n",
       "      <td>2022-01-03|2022-01-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>921</th>\n",
       "      <td>921</td>\n",
       "      <td>17</td>\n",
       "      <td>2022-01-28 00:00:00.000000</td>\n",
       "      <td>106</td>\n",
       "      <td>27</td>\n",
       "      <td>2022-01-24|2022-01-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1008</th>\n",
       "      <td>1008</td>\n",
       "      <td>19</td>\n",
       "      <td>2022-01-03 00:00:00.000000</td>\n",
       "      <td>184</td>\n",
       "      <td>15</td>\n",
       "      <td>2022-01-03|2022-01-09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      index  shopid                 create_time  total_num  td_num  \\\n",
       "4349   4349      78  2022-02-09 00:00:00.000000        251      28   \n",
       "1120   1120      21  2022-01-03 00:00:00.000000        193      27   \n",
       "392     392       8  2022-01-03 00:00:00.000000        173      26   \n",
       "921     921      17  2022-01-28 00:00:00.000000        106      27   \n",
       "1008   1008      19  2022-01-03 00:00:00.000000        184      15   \n",
       "\n",
       "                 week_range  \n",
       "4349  2022-02-07|2022-02-13  \n",
       "1120  2022-01-03|2022-01-09  \n",
       "392   2022-01-03|2022-01-09  \n",
       "921   2022-01-24|2022-01-30  \n",
       "1008  2022-01-03|2022-01-09  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6c65c22",
   "metadata": {},
   "source": [
    "### 计算每周的退单率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ba0e863f",
   "metadata": {},
   "outputs": [],
   "source": [
    "td_rate_df = df2.groupby(by=[\"shopid\",\"week_range\"],as_index=False).agg({\"total_num\":\"sum\",\"td_num\":\"sum\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "9ea22e52",
   "metadata": {},
   "outputs": [],
   "source": [
    "td_rate_df[\"td_rate\"] = td_rate_df[\"td_num\"]/td_rate_df[\"total_num\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "8edf4015",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>shopid</th>\n",
       "      <th>week_range</th>\n",
       "      <th>total_num</th>\n",
       "      <th>td_num</th>\n",
       "      <th>td_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>256</th>\n",
       "      <td>33</td>\n",
       "      <td>2022-01-03|2022-01-09</td>\n",
       "      <td>1757</td>\n",
       "      <td>129</td>\n",
       "      <td>0.073421</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     shopid             week_range  total_num  td_num   td_rate\n",
       "256      33  2022-01-03|2022-01-09       1757     129  0.073421"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "td_rate_df.sample(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff093038",
   "metadata": {},
   "source": [
    "### 计算每周总体的退单率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "4596db69",
   "metadata": {},
   "outputs": [],
   "source": [
    "week_td_rate_df = df2.groupby(by=\"week_range\",as_index=False).agg({\"total_num\":\"sum\",\"td_num\":\"sum\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e69f5470",
   "metadata": {},
   "outputs": [],
   "source": [
    "week_td_rate_df[\"week_td_rate\"] = week_td_rate_df[\"td_num\"]/week_td_rate_df[\"total_num\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "333fe5c2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week_range</th>\n",
       "      <th>total_num</th>\n",
       "      <th>td_num</th>\n",
       "      <th>week_td_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-01-03|2022-01-09</td>\n",
       "      <td>112189</td>\n",
       "      <td>10931</td>\n",
       "      <td>0.097434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-01-10|2022-01-16</td>\n",
       "      <td>111791</td>\n",
       "      <td>10655</td>\n",
       "      <td>0.095312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-01-17|2022-01-23</td>\n",
       "      <td>113399</td>\n",
       "      <td>10724</td>\n",
       "      <td>0.094569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-01-24|2022-01-30</td>\n",
       "      <td>113776</td>\n",
       "      <td>11072</td>\n",
       "      <td>0.097314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-01-31|2022-02-06</td>\n",
       "      <td>112758</td>\n",
       "      <td>10853</td>\n",
       "      <td>0.096250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2022-02-07|2022-02-13</td>\n",
       "      <td>107143</td>\n",
       "      <td>10955</td>\n",
       "      <td>0.102247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2022-02-14|2022-02-20</td>\n",
       "      <td>111946</td>\n",
       "      <td>10989</td>\n",
       "      <td>0.098163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2022-02-21|2022-02-27</td>\n",
       "      <td>112292</td>\n",
       "      <td>10810</td>\n",
       "      <td>0.096267</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              week_range  total_num  td_num  week_td_rate\n",
       "0  2022-01-03|2022-01-09     112189   10931      0.097434\n",
       "1  2022-01-10|2022-01-16     111791   10655      0.095312\n",
       "2  2022-01-17|2022-01-23     113399   10724      0.094569\n",
       "3  2022-01-24|2022-01-30     113776   11072      0.097314\n",
       "4  2022-01-31|2022-02-06     112758   10853      0.096250\n",
       "5  2022-02-07|2022-02-13     107143   10955      0.102247\n",
       "6  2022-02-14|2022-02-20     111946   10989      0.098163\n",
       "7  2022-02-21|2022-02-27     112292   10810      0.096267"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "week_td_rate_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1aabf42",
   "metadata": {},
   "source": [
    "### 统计每件商品高于周退单率的次数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "4046ff65",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_df = pd.merge(\n",
    "    td_rate_df,week_td_rate_df[[\"week_range\",\"week_td_rate\"]],\n",
    "    on=\"week_range\",\n",
    "    how=\"left\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "162fd74f",
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_df[\"td_count\"] = merge_df[[\"td_rate\",\"week_td_rate\"]].apply(lambda x:0 if x[0]<=x[1] else 1,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "a1771838",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>shopid</th>\n",
       "      <th>week_range</th>\n",
       "      <th>total_num</th>\n",
       "      <th>td_num</th>\n",
       "      <th>td_rate</th>\n",
       "      <th>week_td_rate</th>\n",
       "      <th>td_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-03|2022-01-09</td>\n",
       "      <td>1317</td>\n",
       "      <td>158</td>\n",
       "      <td>0.119970</td>\n",
       "      <td>0.097434</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-10|2022-01-16</td>\n",
       "      <td>986</td>\n",
       "      <td>164</td>\n",
       "      <td>0.166329</td>\n",
       "      <td>0.095312</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-17|2022-01-23</td>\n",
       "      <td>1104</td>\n",
       "      <td>117</td>\n",
       "      <td>0.105978</td>\n",
       "      <td>0.094569</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-24|2022-01-30</td>\n",
       "      <td>1293</td>\n",
       "      <td>121</td>\n",
       "      <td>0.093581</td>\n",
       "      <td>0.097314</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-31|2022-02-06</td>\n",
       "      <td>1607</td>\n",
       "      <td>129</td>\n",
       "      <td>0.080274</td>\n",
       "      <td>0.096250</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>2022-02-07|2022-02-13</td>\n",
       "      <td>1515</td>\n",
       "      <td>144</td>\n",
       "      <td>0.095050</td>\n",
       "      <td>0.102247</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>2022-02-14|2022-02-20</td>\n",
       "      <td>1251</td>\n",
       "      <td>140</td>\n",
       "      <td>0.111910</td>\n",
       "      <td>0.098163</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>2022-02-21|2022-02-27</td>\n",
       "      <td>1384</td>\n",
       "      <td>148</td>\n",
       "      <td>0.106936</td>\n",
       "      <td>0.096267</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   shopid             week_range  total_num  td_num   td_rate  week_td_rate  \\\n",
       "0       1  2022-01-03|2022-01-09       1317     158  0.119970      0.097434   \n",
       "1       1  2022-01-10|2022-01-16        986     164  0.166329      0.095312   \n",
       "2       1  2022-01-17|2022-01-23       1104     117  0.105978      0.094569   \n",
       "3       1  2022-01-24|2022-01-30       1293     121  0.093581      0.097314   \n",
       "4       1  2022-01-31|2022-02-06       1607     129  0.080274      0.096250   \n",
       "5       1  2022-02-07|2022-02-13       1515     144  0.095050      0.102247   \n",
       "6       1  2022-02-14|2022-02-20       1251     140  0.111910      0.098163   \n",
       "7       1  2022-02-21|2022-02-27       1384     148  0.106936      0.096267   \n",
       "\n",
       "   td_count  \n",
       "0         1  \n",
       "1         1  \n",
       "2         1  \n",
       "3         0  \n",
       "4         0  \n",
       "5         0  \n",
       "6         1  \n",
       "7         1  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_df[merge_df[\"shopid\"]==1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "cb307167",
   "metadata": {},
   "outputs": [],
   "source": [
    "result_df = merge_df.pivot_table(index=\"shopid\",columns=\"week_range\",values=\"td_count\",margins=True,aggfunc=lambda x:x.sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "4aadd08f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>week_range</th>\n",
       "      <th>2022-01-03|2022-01-09</th>\n",
       "      <th>2022-01-10|2022-01-16</th>\n",
       "      <th>2022-01-17|2022-01-23</th>\n",
       "      <th>2022-01-24|2022-01-30</th>\n",
       "      <th>2022-01-31|2022-02-06</th>\n",
       "      <th>2022-02-07|2022-02-13</th>\n",
       "      <th>2022-02-14|2022-02-20</th>\n",
       "      <th>2022-02-21|2022-02-27</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shopid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>43</td>\n",
       "      <td>41</td>\n",
       "      <td>41</td>\n",
       "      <td>36</td>\n",
       "      <td>45</td>\n",
       "      <td>41</td>\n",
       "      <td>43</td>\n",
       "      <td>41</td>\n",
       "      <td>331</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>81 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "week_range  2022-01-03|2022-01-09  2022-01-10|2022-01-16  \\\n",
       "shopid                                                     \n",
       "1                               1                      1   \n",
       "2                               1                      0   \n",
       "3                               1                      0   \n",
       "4                               1                      1   \n",
       "5                               0                      1   \n",
       "...                           ...                    ...   \n",
       "77                              0                      1   \n",
       "78                              0                      1   \n",
       "79                              1                      1   \n",
       "80                              1                      0   \n",
       "All                            43                     41   \n",
       "\n",
       "week_range  2022-01-17|2022-01-23  2022-01-24|2022-01-30  \\\n",
       "shopid                                                     \n",
       "1                               1                      0   \n",
       "2                               1                      0   \n",
       "3                               0                      1   \n",
       "4                               1                      0   \n",
       "5                               0                      1   \n",
       "...                           ...                    ...   \n",
       "77                              0                      1   \n",
       "78                              0                      0   \n",
       "79                              0                      0   \n",
       "80                              0                      1   \n",
       "All                            41                     36   \n",
       "\n",
       "week_range  2022-01-31|2022-02-06  2022-02-07|2022-02-13  \\\n",
       "shopid                                                     \n",
       "1                               0                      0   \n",
       "2                               0                      1   \n",
       "3                               0                      1   \n",
       "4                               0                      0   \n",
       "5                               0                      0   \n",
       "...                           ...                    ...   \n",
       "77                              1                      0   \n",
       "78                              0                      0   \n",
       "79                              0                      1   \n",
       "80                              1                      0   \n",
       "All                            45                     41   \n",
       "\n",
       "week_range  2022-02-14|2022-02-20  2022-02-21|2022-02-27  All  \n",
       "shopid                                                         \n",
       "1                               1                      1    5  \n",
       "2                               1                      0    4  \n",
       "3                               1                      1    5  \n",
       "4                               1                      1    5  \n",
       "5                               0                      0    2  \n",
       "...                           ...                    ...  ...  \n",
       "77                              1                      0    4  \n",
       "78                              0                      1    2  \n",
       "79                              1                      0    4  \n",
       "80                              0                      0    3  \n",
       "All                            43                     41  331  \n",
       "\n",
       "[81 rows x 9 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "94921afc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5      19\n",
       "4      17\n",
       "2      14\n",
       "3      13\n",
       "6      12\n",
       "7       4\n",
       "1       1\n",
       "331     1\n",
       "Name: All, dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df[\"All\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff5ccc68",
   "metadata": {},
   "source": [
    "### 高于5次的商品为异常商品"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "1ec029ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "unnormal_shop_df = result_df[result_df[\"All\"]>5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "f4355a2e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>week_range</th>\n",
       "      <th>2022-01-03|2022-01-09</th>\n",
       "      <th>2022-01-10|2022-01-16</th>\n",
       "      <th>2022-01-17|2022-01-23</th>\n",
       "      <th>2022-01-24|2022-01-30</th>\n",
       "      <th>2022-01-31|2022-02-06</th>\n",
       "      <th>2022-02-07|2022-02-13</th>\n",
       "      <th>2022-02-14|2022-02-20</th>\n",
       "      <th>2022-02-21|2022-02-27</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shopid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>43</td>\n",
       "      <td>41</td>\n",
       "      <td>41</td>\n",
       "      <td>36</td>\n",
       "      <td>45</td>\n",
       "      <td>41</td>\n",
       "      <td>43</td>\n",
       "      <td>41</td>\n",
       "      <td>331</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "week_range  2022-01-03|2022-01-09  2022-01-10|2022-01-16  \\\n",
       "shopid                                                     \n",
       "9                               1                      0   \n",
       "10                              1                      1   \n",
       "16                              1                      1   \n",
       "19                              0                      1   \n",
       "20                              1                      1   \n",
       "22                              1                      1   \n",
       "24                              1                      1   \n",
       "30                              1                      1   \n",
       "40                              1                      1   \n",
       "41                              1                      1   \n",
       "52                              1                      1   \n",
       "56                              1                      1   \n",
       "57                              1                      1   \n",
       "58                              0                      1   \n",
       "61                              0                      1   \n",
       "73                              1                      1   \n",
       "All                            43                     41   \n",
       "\n",
       "week_range  2022-01-17|2022-01-23  2022-01-24|2022-01-30  \\\n",
       "shopid                                                     \n",
       "9                               1                      1   \n",
       "10                              0                      1   \n",
       "16                              1                      0   \n",
       "19                              0                      1   \n",
       "20                              1                      0   \n",
       "22                              0                      0   \n",
       "24                              1                      0   \n",
       "30                              1                      1   \n",
       "40                              1                      1   \n",
       "41                              0                      1   \n",
       "52                              1                      1   \n",
       "56                              1                      0   \n",
       "57                              1                      1   \n",
       "58                              1                      1   \n",
       "61                              1                      1   \n",
       "73                              0                      1   \n",
       "All                            41                     36   \n",
       "\n",
       "week_range  2022-01-31|2022-02-06  2022-02-07|2022-02-13  \\\n",
       "shopid                                                     \n",
       "9                               1                      0   \n",
       "10                              0                      1   \n",
       "16                              1                      1   \n",
       "19                              1                      1   \n",
       "20                              1                      1   \n",
       "22                              1                      1   \n",
       "24                              1                      0   \n",
       "30                              1                      1   \n",
       "40                              1                      0   \n",
       "41                              0                      1   \n",
       "52                              1                      0   \n",
       "56                              1                      1   \n",
       "57                              1                      1   \n",
       "58                              0                      1   \n",
       "61                              1                      1   \n",
       "73                              1                      1   \n",
       "All                            45                     41   \n",
       "\n",
       "week_range  2022-02-14|2022-02-20  2022-02-21|2022-02-27  All  \n",
       "shopid                                                         \n",
       "9                               1                      1    6  \n",
       "10                              1                      1    6  \n",
       "16                              1                      1    7  \n",
       "19                              1                      1    6  \n",
       "20                              1                      1    7  \n",
       "22                              1                      1    6  \n",
       "24                              1                      1    6  \n",
       "30                              0                      0    6  \n",
       "40                              0                      1    6  \n",
       "41                              1                      1    6  \n",
       "52                              0                      1    6  \n",
       "56                              1                      0    6  \n",
       "57                              0                      1    7  \n",
       "58                              1                      1    6  \n",
       "61                              1                      0    6  \n",
       "73                              1                      1    7  \n",
       "All                            43                     41  331  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unnormal_shop_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96226192",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32a065ec",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3edd30ca",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
